DROP TABLE IF EXISTS `user_department`;
CREATE TABLE `user_department`
(
    `departmentId`       bigint(20) unsigned                     NOT NULL COMMENT '部门ID',
    `departmentName`     varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门名称',
    `parentId`           bigint(20) unsigned                     NOT NULL DEFAULT '0' COMMENT '上级部门ID',
    `departmentTypeKey`  varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门类型关键字',
    `departmentTypeName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门类型名称',
    `remark`             text COLLATE utf8mb4_unicode_ci COMMENT '说明',
    `directorUserId`     bigint(20) unsigned                     NOT NULL DEFAULT '0' COMMENT '主管领导用户id',
    `directorUserName`   varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '主管领导用户姓名',
    `chargeUserId`       bigint(20) unsigned                     NOT NULL DEFAULT '0' COMMENT '分管领导用户id',
    `chargeUserName`     varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '分管领导用户姓名',
    `updateTime`         datetime                                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `orderNum`           int(11)                                 NOT NULL DEFAULT '0' COMMENT '排序值',
    `version`            bigint(20) unsigned                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`departmentId`),
    KEY `parentId` (`parentId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-部门';

DROP TABLE IF EXISTS `user_department_duty_distribution`;
CREATE TABLE `user_department_duty_distribution`
(
    `departmentId` bigint(20) unsigned NOT NULL COMMENT '部门ID',
    `dutyId`       bigint(20) unsigned NOT NULL COMMENT '职位ID',
    PRIMARY KEY (`departmentId`, `dutyId`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-部门职位分配';

DROP TABLE IF EXISTS `user_department_parents`;
CREATE TABLE `user_department_parents`
(
    `departmentId` bigint(20) unsigned NOT NULL COMMENT '部门ID',
    `parentId`     bigint(20) unsigned NOT NULL COMMENT '上级部门ID',
    PRIMARY KEY (`departmentId`, `parentId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-部门关系';

DROP TABLE IF EXISTS `user_department_type`;
CREATE TABLE `user_department_type`
(
    `departmentTypeKey`  varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门类型关键字',
    `departmentTypeName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门类型名称',
    `remark`             text COLLATE utf8mb4_unicode_ci COMMENT '说明',
    `orderNum`           int(11)                                 NOT NULL DEFAULT '0' COMMENT '排序值',
    `version`            bigint(20) unsigned                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`departmentTypeKey`),
    UNIQUE KEY `departmentTypeName` (`departmentTypeName`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-部门类型';

DROP TABLE IF EXISTS `user_duty`;
CREATE TABLE `user_duty`
(
    `dutyId`     bigint(20) unsigned                     NOT NULL COMMENT '职位ID',
    `dutyName`   varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '职位名称',
    `remark`     text COLLATE utf8mb4_unicode_ci COMMENT '说明',
    `updateTime` datetime                                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `orderNum`   int(11)                                 NOT NULL DEFAULT '0' COMMENT '排序值',
    `version`    bigint(20) unsigned                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`dutyId`) USING BTREE,
    UNIQUE KEY `positionName` (`dutyName`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-职位';

DROP TABLE IF EXISTS `user_duty_permission`;
CREATE TABLE `user_duty_permission`
(
    `departmentId`   bigint(20) unsigned                     NOT NULL COMMENT '部门ID',
    `dutyId`         bigint(20) unsigned                     NOT NULL COMMENT '职位ID',
    `permissionKey`  varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限ID',
    `departmentDuty` varchar(41) COLLATE utf8mb4_unicode_ci  NOT NULL COMMENT '部门职位',
    PRIMARY KEY (`departmentId`, `dutyId`, `permissionKey`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-职位权限';

DROP TABLE IF EXISTS `user_login_log`;
CREATE TABLE `user_login_log`
(
    `loginLogId`    bigint(20) unsigned NOT NULL COMMENT '登录记录ID',
    `userId`        bigint(20) unsigned NOT NULL COMMENT '用户ID',
    `name`          varchar(255)        NOT NULL COMMENT '姓名',
    `loginIdentity` varchar(255)        NOT NULL COMMENT '登录身份',
    `loginType`     varchar(255)        NOT NULL COMMENT '登录方式',
    `loginIp`       varchar(19)         NOT NULL COMMENT '登录IP',
    `loginTime`     datetime            NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '登录时间',
    `success`       bit(1)              NOT NULL DEFAULT b'0' COMMENT '登录成功标志',
    `reason`        varchar(255)        NOT NULL COMMENT '失败原因',
    PRIMARY KEY (`loginLogId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户-登录记录';


DROP TABLE IF EXISTS `user_menu`;
CREATE TABLE `user_menu`
(
    `menuId`        bigint(20) unsigned                     NOT NULL COMMENT '菜单ID',
    `parentId`      bigint(20) unsigned                     NOT NULL DEFAULT '0' COMMENT '上级ID',
    `menuKey`       varchar(171) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '菜单名称',
    `displayName`   varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '菜单显示名称',
    `domain`        varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '域',
    `module`        varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '模块',
    `path`          varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '访问路径',
    `activeKey`     varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '激活菜单Key',
    `componentPath` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '组件路径',
    `redirectUrl`   varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '重定向地址',
    `icon`          varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '图标',
    `iconCls`       varchar(255) COLLATE utf8mb4_unicode_ci          DEFAULT NULL COMMENT '图标类名',
    `hidden`        bit(1)                                  NOT NULL DEFAULT b'0' COMMENT '是否隐藏',
    `leaf`          bit(1)                                  NOT NULL DEFAULT b'0' COMMENT '是否展示叶节点',
    `orderNum`      int(11)                                 NOT NULL DEFAULT '0' COMMENT '排序值',
    `version`       bigint(20) unsigned                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`menuId`),
    UNIQUE KEY `menuKey` (`menuKey`),
    KEY `parentId` (`parentId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-菜单';

DROP TABLE IF EXISTS `user_menu_permission`;
CREATE TABLE `user_menu_permission`
(
    `permissionKey` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限关键字',
    `menuId`        bigint(20) unsigned                     NOT NULL COMMENT '菜单ID',
    PRIMARY KEY (`permissionKey`, `menuId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-菜单权限';

DROP TABLE IF EXISTS `user_oauth`;
CREATE TABLE `user_oauth`
(
    `userId`       bigint(20) unsigned NOT NULL COMMENT '用户id',
    `type`         varchar(100)        NOT NULL COMMENT '类型',
    `thirdPartyId` varchar(100)        NOT NULL COMMENT '第三方ID',
    PRIMARY KEY (`userId`, `type`, `thirdPartyId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户-oauth信息';

DROP TABLE IF EXISTS `user_operation_log`;
CREATE TABLE `user_operation_log`
(
    `operationLogId`   bigint(20) unsigned                                           NOT NULL COMMENT '操作记录ID',
    `applicationName`  varchar(255)                                                  NOT NULL COMMENT '应用名称',
    `operationTime`    datetime                                                      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    `operationContent` varchar(255)                                                  NOT NULL COMMENT '操作内容',
    `operationParams`  longtext                                                      NOT NULL COMMENT '操作参数',
    `userId`           bigint(20) unsigned                                           NOT NULL COMMENT '用户ID',
    `username`         varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
    `name`             varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
    PRIMARY KEY (`operationLogId`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户-操作记录';

DROP TABLE IF EXISTS `user_permission`;
CREATE TABLE `user_permission`
(
    `permissionKey`  varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限关键字',
    `permissionName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限名称',
    `remark`         text COLLATE utf8mb4_unicode_ci COMMENT '权限说明',
    `version`        bigint(20) unsigned                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`permissionKey`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-权限';

DROP TABLE IF EXISTS `user_position`;
CREATE TABLE `user_position`
(
    `userId`       bigint(20) unsigned NOT NULL COMMENT '用户id',
    `departmentId` bigint(20) unsigned NOT NULL COMMENT '部门ID',
    `dutyId`       bigint(20) unsigned NOT NULL COMMENT '职位ID',
    PRIMARY KEY (`userId`, `departmentId`, `dutyId`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-任职信息';

DROP TABLE IF EXISTS `user_user`;
CREATE TABLE `user_user`
(
    `userId`                        bigint(20) unsigned                                     NOT NULL COMMENT '用户id',
    `username`                      varchar(100) COLLATE utf8mb4_unicode_ci                 NOT NULL COMMENT '用户名',
    `password`                      char(60) COLLATE utf8mb4_unicode_ci                     NOT NULL COMMENT '密钥',
    `name`                          varchar(100) COLLATE utf8mb4_unicode_ci                 NOT NULL COMMENT '姓名',
    `departmentId`                  bigint(20) unsigned                                     NOT NULL COMMENT '部门ID',
    `departmentName`                varchar(100) COLLATE utf8mb4_unicode_ci                 NOT NULL COMMENT '部门名称',
    `mainDutyId`                    bigint(20) unsigned                                     NOT NULL DEFAULT '0' COMMENT '主职务ID',
    `mainDutyName`                  varchar(100) COLLATE utf8mb4_unicode_ci                 NOT NULL DEFAULT '' COMMENT '主职务名称',
    `gender`                        enum ('MAN','WOMAN','OTHER') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'OTHER' COMMENT '性别',
    `avatar`                        varchar(100) COLLATE utf8mb4_unicode_ci                 NOT NULL COMMENT '头像',
    `email`                         varchar(50) COLLATE utf8mb4_unicode_ci                  NOT NULL COMMENT '电子邮箱',
    `phone`                         varchar(20) COLLATE utf8mb4_unicode_ci                  NOT NULL COMMENT '电话',
    `registrationTime`              datetime                                                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    `lastLoginTime`                 datetime                                                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后登录时间',
    `enabled`                       bit(1)                                                  NOT NULL DEFAULT b'1' COMMENT '帐号启用',
    `locked`                        bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '帐号锁定',
    `admin`                         bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '管理员标志',
    `updateTime`                    datetime                                                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `enableTwoFactorAuthentication` bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '启用双因素认证标志',
    `twoFactorAuthenticationToken`  varchar(255) COLLATE utf8mb4_unicode_ci                 NOT NULL COMMENT '双因素认证token',
    `version`                       bigint(20) unsigned                                     NOT NULL DEFAULT '1' COMMENT '乐观锁版本',
    PRIMARY KEY (`userId`) USING BTREE,
    UNIQUE KEY `username` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC COMMENT ='用户-用户';

BEGIN;
INSERT INTO `user_department`
VALUES (1, 'guerlab', 0, '', '', '', 0, '', 0, '', now(), 0, 1);
INSERT INTO `user_user`
VALUES (1, 'admin', '$2a$10$9moyoFL/cyiKIZo.3jhPM.HKqPlayP.c7RBbc5xDTEyUTu/78csqW', 'admin', 1, 'guerlab', 0, '',
        'OTHER', '/avatar/default.png', '', '', now(), now(), b'1', b'1', now(), b'0', '', 1);
INSERT INTO `user_position`
VALUES (1, 1, 3);
COMMIT;
